package com.data.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.data.dto.ExcelDto;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: wangmq
 * @create: 2024-01-11 13:59
 **/
@RestController
@RequestMapping("/excel")
public class ExcelConvertController {

    @GetMapping("/fileCovert")
    public void fileCovert(HttpServletResponse response){
        File file = new File("/Users/tim/file/江北区域.json");
        Reader reader  =   null ;
        try  {
            System.out.println( " 以字符为单位读取文件内容，一次读一个字节： " );
            //  一次读一个字符
            reader  =   new InputStreamReader( new FileInputStream(file));
            int  tempchar;
            StringBuffer buffer = new StringBuffer();
            while  ((tempchar  =  reader.read())  !=   - 1 ) {
                //  对于windows下，\r\n这两个字符在一起时，表示一个换行。
                //  但如果这两个字符分开显示时，会换两次行。
                //  因此，屏蔽掉\r，或者屏蔽\n。否则，将会多出很多空行。
                if  ((( char ) tempchar)  !=   '\r' ) {
                    System.out.print(( char ) tempchar);
                    buffer.append((char) tempchar);
                }
            }
            reader.close();


            // 创建Excel文档
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("Sheet1");

            // 创建表头
            XSSFRow header = sheet.createRow(0);
            header.createCell(0).setCellValue("cardId");
            header.createCell(1).setCellValue("cardName");
            header.createCell(2).setCellValue("cardVisible");
            header.createCell(3).setCellValue("cardOrder");
            header.createCell(4).setCellValue("cardStyle");

            JSONArray json = JSONArray.parseArray(buffer.toString());
            if(json.size()>0){
                for(int i=0;i<json.size();i++){
                    JSONObject job = json.getJSONObject(i);
                    //System.out.println(job.get("name")+"=") ;

                    XSSFRow row = sheet.createRow(1+i);
                    row.createCell(0).setCellValue(job.get("cardId")+"");
                    row.createCell(1).setCellValue(job.get("cardName")+"");
                    row.createCell(2).setCellValue(job.get("cardVisible")+"");
                    row.createCell(3).setCellValue(job.get("cardOrder")+"");
                    row.createCell(4).setCellValue(job.get("cardStyle")+"");
                }
            }

            // 设置响应头信息
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode("江北区域", "utf-8") + ".csv");

            // 将Excel文档写入响应流中
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }  catch  (Exception e) {
            e.printStackTrace();
        }
    }

    @GetMapping("/csvConvertJson")
    public void csvConvertJson(HttpServletResponse response) {
        File file = new File("/Users/tim/file/城区.xlsx");
        try {
            //创建工作簿
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            StringBuffer buffer = new StringBuffer("[");
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                ExcelDto dto = new ExcelDto();
                buffer.append("{");
                //获取第i行的数据
                XSSFRow row = sheet.getRow(i);
                XSSFCell cell0 = row.getCell(0);
                dto.setA(cell0.getStringCellValue());
                buffer.append("\"cardId\":\""+cell0.getStringCellValue()+"\",");
                XSSFCell cell1 = row.getCell(1);
                buffer.append("\"cardName\":\""+cell1.getStringCellValue()+"\",");
                XSSFCell cell2 = row.getCell(2);
                buffer.append("\"cardVisible\":"+(int)cell2.getNumericCellValue()+",");
                XSSFCell cell3 = row.getCell(3);
                buffer.append("\"cardOrder\":"+(int)cell3.getNumericCellValue()+",");
                XSSFCell cell4 = row.getCell(4);
                buffer.append("\"cardStyle\":\""+cell4.getStringCellValue()+"\"");
                if(i==sheet.getLastRowNum()){
                    buffer.append("}");
                }else{
                    buffer.append("},");
                }
            }
            buffer.append("]");
            System.out.println(buffer.toString());
            File f = new File("/Users/tim/file/城区1.json");
            Writer write = new OutputStreamWriter(new FileOutputStream(f), "UTF-8");
            write.write(buffer.toString());
            write.flush();
            write.close();

            // 设置响应头信息
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode("data", "utf-8") + ".json");

            File f2 = new File("/Users/tim/file/城区1.json");
            FileInputStream in = new FileInputStream(f2);
            ServletOutputStream outputStream = response.getOutputStream();
            int len = 0;
            byte[] buf = new byte[1024];
            while ((len = in.read(buf)) > 0) {
                outputStream.write(buf, 0, len);
            }
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
